IF NOT EXISTS (SELECT NAME FROM SYS.DATABASES WHERE NAME = 'Blue_Pumbkin')
	
	CREATE DATABASE Blue_Pumbkin
	
ELSE

	USE Blue_Pumbkin
GO

IF NOT EXISTS(SELECT NAME FROM SYS.TABLES WHERE NAME='Employee' )
	CREATE TABLE Employee(
		EmpId int identity primary key,
		UserName nvarchar(50) not null,
		[Password] nvarchar(50),
		Fullname nvarchar(50),
		Birthdate smalldatetime,
		Gender nvarchar(20) NOT NULL CHECK(Gender = 'Male' OR Gender = 'Female'),
		[Address] nvarchar(100),
		IdentityCard int,
		PhoneNumber int,
		Email nvarchar(50),
		JoinDate smalldatetime,
		Position nvarchar(50),
		[Status] nvarchar(3),
		isAdmin int
	)
GO

IF NOT EXISTS(SELECT NAME FROM SYS.TABLES WHERE NAME='EventType' )

	CREATE TABLE EventType(
		Id int identity primary key,
		Name nvarchar(50),
		[Description] nvarchar(100)
	)

GO

IF NOT EXISTS(SELECT NAME FROM SYS.TABLES WHERE NAME='[Event]' )
	CREATE TABLE [Event](
		EventId int identity primary key,
		Title nvarchar(50),
		[Type] int foreign key references EventType(Id),
		[Image] nvarchar(50),
		[Description] nvarchar(100),
		Startdate smalldatetime,
		Enddate smalldatetime,
		[Status] nvarchar(10)
	)

GO

IF NOT EXISTS(SELECT NAME FROM SYS.TABLES WHERE NAME='Prize' )
	CREATE TABLE Prize(
		Id int identity primary key,
		Name nvarchar(50),
		[Image] nvarchar(50),
		[Description] nvarchar(50)
	)
GO

IF NOT EXISTS(SELECT NAME FROM SYS.TABLES WHERE NAME='RegisterEvent')
	CREATE TABLE RegisterEvent(
		Id int identity primary key,
		EmpId int foreign key references Employee(EmpId),
		EventId int foreign key references [Event](EventId),
		Timejoin smalldatetime,
		[Description] nvarchar(100)
	)
GO

IF NOT EXISTS(SELECT NAME FROM SYS.TABLES WHERE NAME='Emp_Prize' )
	CREATE TABLE Emp_Prize(
		Id int references RegisterEvent(Id),
		PrizeId int references Prize(Id),
		constraint pk_PrizeEvent primary key (Id,PrizeId)
	)

IF NOT EXISTS(SELECT NAME FROM SYS.TABLES WHERE NAME='FAQ' )
	CREATE TABLE FAQ(
		Id int identity primary key,
		Question text,
		Answer text,
	)
GO

IF NOT EXISTS(SELECT NAME FROM SYS.TABLES WHERE NAME='Contact' )
	CREATE TABLE Contact(
		Id int identity primary key,
		Name nvarchar(50),
		Email nvarchar(50),
		[Subject] nvarchar(50),
		[Message] text
	)
GO
